using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Common;
using TVT.Common.Entities;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Transactions;
using TVT.Common.Exceptions;
using DataAccess;
using TVT.Common.Helpers;
using System.Data.SqlClient;
using System.Configuration;
using System.Reflection;
using System.IO;
using Microsoft.Practices.EnterpriseLibrary.Common.Configuration;
using Common.Helpers;

namespace TVT.DataAccess.Repositories
{
    public class DBRepository

    {
        protected Database db;
        private static DBRepository instance = null;

        protected DBRepository()
        {
            db = DatabaseFactory.CreateDatabase();           
        }

        public static DBRepository GetInstance()
        {
            if (DBRepository.instance == null)
            {
                DBRepository.instance = new DBRepository();
            }
            return DBRepository.instance;
        }

        #region Account

        public virtual int SaveAccount(int id, String name, decimal interest, int idCompany, int idClearing)
        {
            if (id == 0)
            {
                return Int32.Parse(db.ExecuteScalar("InsertAccount", name, interest, idCompany, idClearing).ToString());
            }
            else
            {
                db.ExecuteNonQuery("UpdateAccount", id, name, interest, idCompany, idClearing);
                return id;
            }
        }

        public virtual void SaveProductAccount(int idAccount, int idProduct)
        {
            db.ExecuteNonQuery("SaveProductAccount", idAccount, idProduct);
        }

        public virtual void DeleteAccountByProduct(int idProduct)
        {
            db.ExecuteNonQuery("DeleteAccountByProduct", idProduct);
        }

        public virtual DataTable FindAllAccounts()
        {
            try
            {
                return db.ExecuteDataSet("FindAllAccounts").Tables[0];
            }
            catch (ArgumentOutOfRangeException)
            {
                throw new FunctionalException(Resources.AccountNotFound);
            }
        }

        public virtual DataTable FindAllAccountsByCompanies(List<int> idCompanies)
        {
            string companies = IdsToString(idCompanies);

            return db.ExecuteDataSet("FindAllAccountsByCompanies",companies).Tables[0];
        }

        public virtual DataTable FindAccountsByUser(Guid userId)
        {
            try
            {
                return db.ExecuteDataSet("FindAccountsByUser",userId).Tables[0];
            }
            catch (ArgumentOutOfRangeException)
            {
                throw new FunctionalException(Resources.AccountNotFound);
            }
        }

        public virtual DataTable FindAccount(int idAccount)
        {
            try
            {
                return db.ExecuteDataSet("FindAccount", idAccount).Tables[0];
            }
            catch (ArgumentOutOfRangeException)
            {
                throw new FunctionalException(Resources.AccountNotFound);
            }
        }

        public virtual DataTable FindAccountByIdProduct(int idProduct)
        {
            try
            {
                return db.ExecuteDataSet("FindAccountByIdProduct", idProduct).Tables[0];
            }
            catch (ArgumentOutOfRangeException)
            {
                throw new FunctionalException(Resources.AccountNotFound);
            }
        }

        public virtual DataTable FindAccountIdByName(string name)
        {
            return db.ExecuteDataSet("FindAccountIdByName", name).Tables[0];
            
        }

        public virtual void DeleteAccount(int id)
        {
            db.ExecuteNonQuery("DeleteAccount", id);
        }

        public virtual DataTable FindAccountsByUserAndIdClearings(Guid userId, List<int> idsClearing)
        {
            string clearings = IdsToString(idsClearing);
            return db.ExecuteDataSet("FindAccountsByUserAndIdClearings", userId, clearings).Tables[0];
        }

        #endregion
   
        #region User

        public virtual void SaveUser(Guid userId, string firstName, string lastName, string im, string phone1)
        {
            try
            {
                db.ExecuteScalar("InsertUser", userId, firstName, lastName, im, phone1);
            }
            catch (SqlException ex)
            {
                throw new FunctionalException();
            }
        }

        public virtual void UpdateUser(Guid userId, string firstName, string lastName, string im, string phone1)
        {
            try
            {
                db.ExecuteNonQuery("UpdateUser", userId, firstName, lastName, im, phone1);
            }
            catch (SqlException)
            {
                throw new FunctionalException();
            }

        }

        public virtual DataTable FindAllUsers()
        {
            return db.ExecuteDataSet("FindAllUsers").Tables[0];
        }
            

        private string IdsToString(List<int> list)
        {
            bool isFirst = true;
            string param = "(";

            foreach (int id in list)
            {
                if (isFirst)
                {
                    isFirst = false;
                    param += id.ToString();
                }
                else
                {
                    param += "," + id.ToString();
                }
            }

            param += ")";
            return param;
        }


        public virtual DataTable FindMatchs(Guid userId)
        {
            return db.ExecuteDataSet("FindMatchs", userId).Tables[0];
        }

        public virtual DataTable FindUser(Guid userId)
        {
            try
            {
                return db.ExecuteDataSet("FindUser", userId).Tables[0];
            }
            catch (ArgumentOutOfRangeException)
            {
                throw new FunctionalException(Resources.UserNotFound);
            }
        }

        public virtual void DeleteUser(Guid idUser)
        {
            db.ExecuteNonQuery("DeleteUser", idUser);
        }
        public virtual DataTable FindAllUserAliasByIdUser(Guid idUser)
        {
            DataSet result = db.ExecuteDataSet("FindAllUserAliasByIdUser", idUser);
            if (result.Tables.Count == 0)
                return new DataTable();

            return result.Tables[0];
        }
        public virtual DataTable FindUserByAliasPlatformAndName(int idPlatform, string name)
        {
            DataSet result = db.ExecuteDataSet("FindUserByAliasPlatformAndName", idPlatform, name);
            if (result.Tables.Count == 0)
                return new DataTable();

            return result.Tables[0];
        }
        public virtual void DeleteAllUserAliasByIdUser(Guid idUser)
        {
            db.ExecuteNonQuery("DeleteAllUserAliasByIdUser", idUser);
        }

        public virtual int InsertUserAlias(string name, Guid idUser, int idPlatform)
        {
            return Int32.Parse(db.ExecuteScalar("InsertUserAlias", name, idUser, idPlatform).ToString());
        }

        public virtual DataTable FindUserByCompleteName(string firstName, string secondName)
        {
            return db.ExecuteDataSet("FindUserByCompleteName", firstName, secondName).Tables[0];
        }

        #endregion

        #region Company

        public virtual int SaveCompany(String name, decimal accountManagmentFee, String telephone, String address, String taxId, decimal perfFee)
        {
            return Int32.Parse(db.ExecuteScalar("InsertCompany", name, accountManagmentFee, telephone, address, taxId, perfFee).ToString());
        }

        public virtual void UpdateCompany(int id, String name, decimal accountManagmentFee, String telephone, String address, String taxId, decimal perfFee)
        {
            db.ExecuteNonQuery("UpdateCompany", id, name, accountManagmentFee, telephone, address, taxId, perfFee).ToString(); 
        }

        public virtual void DeleteCompany(int id)
        {
            db.ExecuteNonQuery("DeleteCompany", id);
        }

        public virtual DataTable FindAllCompanies()
	    {
		    return db.ExecuteDataSet("FindAllCompanies").Tables[0];
	    }

        public virtual DataTable FindCompanyById(int idCompany)
	    {
            return db.ExecuteDataSet("FindCompanyById", idCompany).Tables[0];
	    }
        public virtual DataTable FindCompaniesByUser(Guid guid)
        {
            return db.ExecuteDataSet("FindCompaniesByUser", guid).Tables[0];
        }

        public virtual DataTable FindCompaniesByIdAccount(int id)
        {
            return db.ExecuteDataSet("FindCompaniesByIdAccount", id).Tables[0];
        }
        #endregion

        #region UserProfile

        public void SaveUserProfile(Guid userId, int high, int weight, bool male, int likes, DateTime birthDate, decimal eigenFaceDistance, String eigenVector)
        {
            db.ExecuteNonQuery("InsertUserProfile", userId, high, weight, male, likes, birthDate, eigenFaceDistance, eigenVector);
        }

        public void UpdateUserProfile(Guid userId, int high, int weight, bool male, int likes, DateTime birthDate, decimal eigenFaceDistance, String eigenVector )
        {
            db.ExecuteNonQuery("UpdateUserProfile", userId, high, weight, male, likes, birthDate, eigenFaceDistance, eigenVector);
        }

        public DataTable FindUserProfile(Guid userId)
        {
            return db.ExecuteDataSet("FindUserProfile", userId).Tables[0];
        }

        #endregion

        #region Comment

        public int SaveComment(Guid userIdFrom, Guid userIdTo, string text)
        {
            return Int32.Parse(db.ExecuteScalar("InsertComment", userIdFrom, userIdTo, text).ToString());
        }

        public void DeleteComment(int id)
        {
            db.ExecuteNonQuery("DeleteComment", id);
        }

        public DataTable FindCommentsToUser(Guid userId)
        {
            return db.ExecuteDataSet("FindCommentToUser", userId).Tables[0];
        }

        #endregion


        #region UserRelation

        public int SaveUserRelation(Guid userId1, Guid userId2, int status, decimal match)
        {
            return Int32.Parse(db.ExecuteScalar("InsertUserRelation", userId1, userId2, status, match).ToString());
        }

        public void UpdateUserRelation(int id, Guid userId1, Guid userId2, int status, decimal match)
        {
            db.ExecuteNonQuery("UpdateUserRelation", id, userId1, userId2, status, match);
        }

        public void DeleteUserRelation(int id)
        {
            db.ExecuteNonQuery("DeleteUserRelation", id);
        }

        public DataTable FindAllUserRelations(Guid userId, int status)
        {
            return db.ExecuteDataSet("FindAllUserRelations", userId, status).Tables[0];
        }

        public DataTable FindUserRelation(Guid userId1, Guid userId2)
        {
            return db.ExecuteDataSet("FindUserRelationByUsers", userId1, userId2).Tables[0];
        }

        public DataTable FindUserRelation(int idUserRelation)
        {
            return db.ExecuteDataSet("FindUserRelation", idUserRelation).Tables[0];
        }

        #endregion


    }
}
